﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;
using System.Configuration;
using System.Data.SqlClient;

namespace Crosland.Automotriz.Logic
{
    public class EjecutarJobsLogic
    {
        //Sql server name
        string sqlServerName = ConfigurationManager.AppSettings["ServidorPRD"];
        //Sql server user name and password that have access to sql agent
        string sqlUserName = ConfigurationManager.AppSettings["UsuarioPRD"];
        string sqluserPassword = ConfigurationManager.AppSettings["PasswordPRD"];
        static Job job;
        static Server server;
        static ServerConnection connection;

        public string StartSqlJob(string jobName)
        {
            string msg = string.Empty;

            //Create a connection to the Sql Server
            connection = new ServerConnection(sqlServerName, sqlUserName, sqluserPassword);

            //Get an instance of the Sql Server
            server = new Server(connection);

            try
            {
                //Get the particular job object using job name
                job = server.JobServer.Jobs[jobName];
                //Check the job state, if it is not running i.e Idle then start the job
                if (job.CurrentRunStatus == JobExecutionStatus.Idle)
                {
                    job.Start();
                    msg = "Proceso de ejecución de paquete Iniciado...!";
                }

                if (job.CurrentRunStatus == JobExecutionStatus.Executing)
                {
                    msg = "El paquete se esta ejecutando...!";
                }

            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            finally 
            {
                Destroyobjects();
            }

            return msg;

        }

        static void Destroyobjects()
        {
            if (job != null)
                job = null;
            if (server != null)
                server = null;
            if (connection != null)
            {
                connection.Disconnect();
                connection = null;
            }
        }

        public int StatusJob(string jobName)
        {
            int id = 0;

            //Create a connection to the Sql Server
            connection = new ServerConnection(sqlServerName, sqlUserName, sqluserPassword);

            //Get an instance of the Sql Server
            server = new Server(connection);

            try
            {
                //Get the particular job object using job name
                job = server.JobServer.Jobs[jobName];
                
                //Check the job state, if it is not running i.e Idle then start the job
                if (job.CurrentRunStatus == JobExecutionStatus.Idle)
                {
                    id = 4;
                    //msg = "Proceso de ejecucion Inactivo...!";
                }
                else if (job.CurrentRunStatus ==JobExecutionStatus.Executing)
                {
                    id = 1;
                    //msg = "Ejecutando paquete...!";
                }
                else if (job.CurrentRunStatus == JobExecutionStatus.Suspended)
                {
                    id = 5;
                    //msg = "Paquete cancelado...!";
                }
                else if (job.CurrentRunStatus == JobExecutionStatus.BetweenRetries)
                {
                    id = 3;
                    //msg = "Entre reintentos";
                }
                else if (job.CurrentRunStatus == JobExecutionStatus.PerformingCompletionAction)
                {
                    //job.LastRunOutcome==CompletionResult.Succeeded
                    id = 7;
                    //msg = "Paquete ejecutado satisfactoriamente..";
                }
            }
            catch (Exception)
            {
                id = -1;
            }

            return id;
        }

        public string MsgStatusJob(int id)
        {
            string msg = string.Empty;

            if (id==4)
            {
                msg = "Proceso terminado...!";
            }
            else if (id==1)
            {
                msg = "Ejecutando proceso...!";
            }
            else if (id==5)
            {
                msg = "Proceso cancelada...!";
            }
            else if (id==3)
            {
                msg = "Entre reintentos";
            }
            else if (id==7)
            {
                msg = "Proceso de actualización ejecutada satisfactoriamente..";
            }
            else if (id == -1)
            {
                msg = "Error al procesar la actualización...";
            }
            return msg;
        
        }

        public string MsgStatusJobGeneral(int id)
        {
            string msg = string.Empty;

            if (id == 4)
            {
                msg = "Actualización terminada...!";
            }
            else if (id == 1)
            {
                msg = "Actualizando información...!";
            }
            else if (id == 5)
            {
                msg = "Actualización cancelada...!";
            }
            else if (id == 3)
            {
                msg = "Entre reintentos";
            }
            else if (id == 7)
            {
                msg = "Proceso de actualización ejecutada satisfactoriamente..";
            }
            else if (id == -1)
            {
                msg = "Error al procesar la actualización...";
            }
            return msg;

        }

        public string EstadoDelJob(string jobName)
        {
            //Create a connection to the Sql Server
            connection = new ServerConnection(sqlServerName, sqlUserName, sqluserPassword);

            //Get an instance of the Sql Server
            server = new Server(connection);

            string estado = string.Empty;

            try
            {
                //Get the particular job object using job name
                job = server.JobServer.Jobs[jobName];
                
                if (job.LastRunOutcome == CompletionResult.Succeeded)
                {
                    estado = "Proceso ejecutado satisfactoriamente...!";
                }
                else if (job.LastRunOutcome == CompletionResult.InProgress)
                {
                    estado = "Ejecutando proceso...!";
                }
                else if (job.LastRunOutcome == CompletionResult.Failed)
                {
                    estado = "Error al ejecutar el proceso.";
                }

            }
            catch (SmoException ex)
            {
                estado = "Error al ejecutar el Job= "+ ex.Message;
            }

            return estado;
        }

    }
}
